<?php
	namespace App\Http\Controllers\Admin;

	use Dompdf\Dompdf;
	use Illuminate\Http\Request;
	use App\Http\Controllers\Controller;
	use DB;
	use Storage;

	class ExcelPosController extends Controller
	{

		//字段在Excel中的位置
		public function excelPos(Request $request)
		{
			$id = $request->id; //a_std_tool_rel 的 id
			if ($request->isMethod('POST')) {
				$post_data = $request->except([
					'id',
					'_token',
					'file',
					'std_id',
					'tool_name',
					'add',
					'pos_id'
				]);
				$pos_id = $request->pos_id;
				// $val = strtoupper($val); //转成大写字母
				foreach ($post_data as $k => $val) {
					$post_data[$k] = strtoupper($val); //转成大写字母
				}
				//            dd($post_data);
				// $post_data['line_num'] = intval($post_data['line_num']); //转成整数
				$old_pos = DB::table('a_excel_pos')->find($request->pos_id);
				if ($old_pos->is_default == 1 && $request->is_default == 0) {
					return '请先指定默认模板！';
				}
				DB::beginTransaction();
				try {
					if (isset($request->add)) {   //添加
						$post_data['std_tool_rel_id'] = $id;
						$pos_id = DB::table('a_excel_pos')->insertGetId($post_data);
					}
					else {   //修改
						//update 返回受影响的行数
						DB::table('a_excel_pos')->where([
							'id' => $pos_id
						])->update($post_data);
					}
					
					if ($request->is_default == 1) {
						//将其他默认模板修改为非默认模板
						DB::table('a_excel_pos')->where('std_tool_rel_id', $id)->where('id', '!=', $pos_id)->update(['is_default' => 0]);
						//+++++++ 修改位置（试验项目，试验数据，试验条件） start +++++++++
							$arrTables = [
								'a_std_tool_item_rel'=>['a_excel_pos_std_tool_item_rel','std_tool_item_rel_id'] , //修改试验标准--工器具--试验项目 位置
								'a_testdata_key'=>['a_excel_pos_testdata_key','testdata_key_id'], //修改试验标准--工器具--试验项目--试验数据 位置
								'a_condition_key'=>['a_excel_pos_condition_key','condition_key_id'], //修改试验标准--工器具--试验项目--试验条件 位置
							];
							foreach($arrTables as $target_tb => $excel_pos_tb_col){
								list($excel_pos_tb,$col) = $excel_pos_tb_col;
								if (DB::table($excel_pos_tb)->where('excel_pos_id',$pos_id)->exists()) {
									$poss = DB::table($excel_pos_tb)
										->where(['excel_pos_id'=>$pos_id])
										->get();
									foreach($poss as $pos){
										$up = ['pos_start'=>$pos->pos_start,'pos_end'=>$pos->pos_end];
										DB::table($target_tb)->where('id',$pos->$col)->update($up);
									}
								}
							}
						//+++++++ 修改位置（试验项目，试验数据，试验条件） end +++++++++
					}
					$o_name = $request->name === '默认' ? '' : $request->name;
					//如果验证通过，继续向下执行...
					if ($request->hasFile('file') && $request->file->isValid()) {
						$path = $request->file->storeAs("ExcelTemplate/GanSuBaiYin", $request->tool_name . $o_name . '.docx', 'public');
					}
					if ($request->name !== $old_pos->name) {
						$old_file = "ExcelTemplate/GanSuBaiYin/".$request->tool_name . $old_pos->name . '.docx';
						$new_file = "ExcelTemplate/GanSuBaiYin/".$request->tool_name . $o_name . '.docx';
						Storage::disk('public')->move($old_file,$new_file);
					}
					DB::commit();
				} catch (\Exception $e) {
					DB::rollBack();
					return '操作失败！' . $e->getMessage();
				}
				/*  //编写验证规则
				$arr = [
					'file' => 'file|mimetypes:application/vnd.ms-excel',//有的xls文件验证不通过，暂注释掉
				];
				//进行验证
				$request->validate($arr); */
				return 1;
			}
			else {
				$sql = "SELECT
                        A.id,
                        A.std_id,
                        A.tool_id,
                        B.num,
                        C.`name`
                    FROM
                        a_std_tool_rel A
                    LEFT JOIN a_standard B ON A.std_id = B.id
                    LEFT JOIN a_tool C ON A.tool_id = C.id
                    WHERE
                        A.id = :id";
				$info_data = DB::select($sql, ['id' => $id]);
				$info_data = $info_data[0]; //绑定的标准
				

				//模板
				$pos_id = $request->pos_id;
				if ($pos_id) {
					$data = DB::table('a_excel_pos')->find($pos_id);
				}
				else {
					$data = DB::table('a_excel_pos')->where('std_tool_rel_id', $id)->where('is_default', 1)->first();
					//判断模板文件是否存在
					if (!$data) {
						$data = DB::table('a_excel_pos')->where('std_tool_rel_id', $id)->first();
						if (!$data) {
							# 如果记录不存在，就插入一条
							$pos_id = DB::table('a_excel_pos')->insertGetId([
								'std_tool_rel_id' => $id,
								'is_default'      => 1
							]);
							$data = DB::table('a_excel_pos')->find($pos_id);
						}
						
					}
				}

				$o_name = $data->name === '默认' ? '' : $data->name;
				$template_file = "./storage/ExcelTemplate/GanSuBaiYin/{$info_data->name}.docx"; //考虑到兼容Linux，斜杠用“/”
				$exist_template = file_exists($template_file);
				if ($request->expectsJson()) {
					$rtdata = [
						'data'           => $data,
						'exist_template' => $exist_template,
						'template_file'  => $template_file
					];
					return $rtdata;
				}
				$data_tmp = DB::table('a_excel_pos')->where('std_tool_rel_id', $id)->get();
				$o_std_rels = DB::table('a_std_tool_rel as a')
						->leftJoin('a_standard as b', 'a.std_id', '=', 'b.id')
						->where('a.tool_id', $info_data->tool_id)->where('a.std_id', '!=', $info_data->std_id)
						->select('a.id', 'b.num')
						->get();  //绑定的其他检测标准，用于同步位置
				return view('admin.excelPos.excelPos', compact('info_data', 'data', 'exist_template', 'data_tmp', 'template_file', 'o_std_rels'));
			}
		}

		//下载模板
		public function downLoadTemplate(Request $request)
		{
			$template_file = "/storage/ExcelTemplate/{$request->std_id}/{$request->name}.xls?id=" . time(); //考虑到兼容Linux，斜杠用“/”
			return redirect($template_file);
		}

		//下载封面 封底模板
		public function downLoadTemplateFMFD(Request $request)
		{
			$template_file = "/storage/ExcelTemplate/{$request->FMFD}.xls?time=".time(); //考虑到兼容Linux，斜杠用“/”
			return redirect($template_file);
		}

		//同步位置
		public function syncPos()
		{
			$fromId = request('from_id', '');
			$toId = request('to_id', '');
			$fromData = DB::table('a_excel_pos')
				->where('std_tool_rel_id',$fromId)
				->select('company','line_num','report_num','come_date','test_date','sample_count','temp','hum','pressure','wea','serial_num1','serial_num2','sample_num1','sample_num2','self_num1','self_num2','voltage1','voltage2','class1','class2','res1','res2','valid_date1','valid_date2','sample_rowspann')
				->first();
			$fromData = (array)$fromData;
			$res = DB::table('a_excel_pos')->where('std_tool_rel_id',$toId)->update($fromData);
			return $res ? 1 : 0;
		}

		public function add(){
			$params = request()->except(['_token','std_id','tool_name','pos_id','file']);
			$exists = DB::table('a_excel_pos')->where(['std_tool_rel_id'=>$params['std_tool_rel_id'],'name'=>$params['name']])->exists();
			if ($exists) {
				return '模板已存在！';
			}
			if ($params['is_default'] == 1) {
				DB::table('a_excel_pos')->where('std_tool_rel_id',$params['std_tool_rel_id'])->update(['is_default'=>0]);
			}
			$res = DB::table('a_excel_pos')->insert($params);
			if (request()->hasFile('file') && request()->file->isValid()) {
				$o_name = request('name');
				$path = request()->file->storeAs("ExcelTemplate/GanSuBaiYin", request('tool_name') . $o_name . '.docx', 'public');
			}
			return $res ? 1 : 0;
		}

		//各个模板 项目结果位置，试验数据位置，试验条件位置
	    public function getTemplPos(){
	        if (request()->isMethod('get')) {
	            $excel_pos_id = request('excel_pos_id','','intval');
	            if (!$excel_pos_id) {
	                return "请先选择模板！";
	            }
	            $type = request('type','');
	            switch ($type)
				{
				case 'item':
					$table = 'a_excel_pos_std_tool_item_rel';
				    break;
				case 'testdata':
					$table = 'a_excel_pos_testdata_key';
				    break;
				case 'condition':
					$table = 'a_excel_pos_condition_key';
				    break;
				default:
					$table = '';
				}
				if (!$table) {
					return '参数错误！';
				}
	            $data = DB::table($table)->where('excel_pos_id',$excel_pos_id)->get()->toArray();
	            return $data;
	        }
	    }
	}
